package com.bj58.ecdata.dao.basedao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.bj58.ecdata.dao.statementcreater.IStatementCreater;
import com.bj58.ecdata.dao.statementcreater.MysqlPSCreater;
import com.bj58.ecdata.dao.statementcreater.SqlServerPSCreater;
import com.bj58.ecdata.dao.util.JdbcUitl;
import com.bj58.ecdata.dbconnectionpool.ConnectionPool;

public class DAOHelper
{
  private static final Logger logger = LoggerFactory.getLogger(DAOHelper.class);
  public IDAO sql = null;
  public IDAO proc = null;
  private ConnectionHelper connHelper;
  
  public DAOHelper(ConnectionHelper connHelper)
  {
    this.connHelper = connHelper;
  }
  
  @Deprecated
  public ConnectionPool getConnPool()
  {
    return this.connHelper.getConnPool();
  }
  
  public ConnectionHelper getConnHelper()
  {
    return this.connHelper;
  }
  
  public Object execInsert(String sql, IPreparedStatementHandler handler)
    throws Exception
  {
    return execWithPara(sql, handler);
  }
  
  public Object execWithPara(String sql, IPreparedStatementHandler handler)
    throws Exception
  {
    Connection conn = null;
    PreparedStatement ps = null;
    try
    {
      conn = this.connHelper.get();
      ps = conn.prepareStatement(sql);
      return handler.exec(ps);
    }
    catch (Exception e)
    {
      logger.error("execQuery error sql:" + sql, e);
      throw e;
    }
    finally
    {
      JdbcUitl.closeStatement(ps);
      this.connHelper.release(conn);
    }
  }
  
  public Object execQuery(String sql, IRowCallbackHandler handler)
    throws Exception
  {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    try
    {
      conn = this.connHelper.getReadConnection();
      
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      return handler.exec(rs);
    }
    catch (Exception e)
    {
      logger.error("execQuery error sql:" + sql, e);
      throw e;
    }
    finally
    {
      JdbcUitl.closeResultSet(rs);
      JdbcUitl.closeStatement(stmt);
      this.connHelper.release(conn);
    }
  }
  
  public Object execProc(String sql, ICallableStatementHandler handler)
    throws Exception
  {
    Connection conn = null;
    CallableStatement cs = null;
    try
    {
      conn = this.connHelper.get();
      cs = conn.prepareCall(sql);
      return handler.exec(cs);
    }
    catch (SQLException e)
    {
      logger.error("execCustomProc error " + sql);
      throw e;
    }
    finally
    {
      JdbcUitl.closeStatement(cs);
      this.connHelper.release(conn);
    }
  }

  /**
   * @deprecated Method execTransaction is deprecated
   */

  public Exception execTransaction(ITransactionHandler handler)
      throws Exception
  {
      Connection conn;
      Exception exception;
      conn = null;
      exception = null;
      try
      {
          IStatementCreater sqlServerCreater = new SqlServerPSCreater();
          IStatementCreater mysqlCreater = new MysqlPSCreater();
          conn = connHelper.get();
          conn.setAutoCommit(false);
          try
          {
              handler.exec(conn, sqlServerCreater, mysqlCreater);
          }
          catch(Exception ex)
          {
              exception = ex;
          }
      }
      catch(SQLException e)
      {
          logger.error((new StringBuilder("execCustomProc error ")).append(sql).toString());
          throw e;
      }
      try
      {
          conn.commit();
      }
      catch(Exception e)
      {
          e.printStackTrace();
      }
      connHelper.release(conn);
      return exception;
  }

  public void execTransaction(ITransaction tran)
      throws Exception
  {
      beginTransaction();
      try
      {
          tran.exec();
          commitTransaction();
      }
      catch(Exception ex)
      {
          rollbackTransaction();
          throw ex;
      }
      endTransaction();
      return;
  }

  public void beginTransaction()
      throws Exception
  {
      beginTransaction(2);
  }

  public void beginTransaction(int level)
      throws Exception
  {
      Connection conn = connHelper.get();
      if(conn != null)
          try
          {
              conn.setAutoCommit(false);
              conn.setTransactionIsolation(level);
              connHelper.lockConn(conn);
          }
          catch(Exception ex)
          {
              logger.error("beginTransaction error..",ex);
          }
      else
          throw new Exception("conn is null when beginTransaction");
  }

  public void commitTransaction()
      throws Exception
  {
      Connection conn = connHelper.get();
      if(conn != null)
          conn.commit();
      else
          throw new Exception("conn is null when commitTransaction");
  }

  public void rollbackTransaction()
      throws Exception
  {
      Connection conn = connHelper.get();
      if(conn != null)
          conn.rollback();
      else
          throw new Exception("conn is null when rollbackTransaction");
  }

  public void endTransaction()
      throws Exception
  {
      Connection conn;
      conn = connHelper.get();
      if(conn == null)
    	  throw new Exception("conn is null when endTransaction");
      conn.setAutoCommit(true);
      conn.setTransactionIsolation(2);
      connHelper.unLockConn();
      connHelper.release(conn);
  }
  
}
